package com.fzy.core.system.service;

import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.fzy.core.BaseService;
import com.fzy.core.Page;

public class DbService extends BaseService {
	public List listTables(DbTableQuery dbTableQuery) {
		String sql = "select a.table_name, b.comments from user_tables a left join user_tab_comments b on a.table_name = b.table_name";
		List params = new LinkedList();
		String orders = "";
		sql += orders;
		return jdbcTemplate.queryForList(sql, params.toArray());
	}
	public List listColumns(String tableName, DbColumnQuery dbColumnQuery) {
		if(StringUtils.isBlank(tableName)) return new LinkedList();
		String sql = "";
		sql += " SELECT   a.table_name,";
		sql += "          a.column_name,";
		sql += "          b.comments,";
		sql += "          a.data_type,";
		sql += "          a.data_length,";
		sql += "          a.data_precision,";
		sql += "          a.data_scale,";
		sql += "          a.nullable,";
		sql += "          a.column_id,";
		sql += "          c.r_table_name,";
		sql += "          c.r_column_name,";
		sql += "          c.constraint_type";
		sql += "   FROM         user_tab_columns a";
		sql += "             LEFT JOIN";
		sql += "                user_col_comments b";
		sql += "             ON     a.table_name = b.table_name";
		sql += "                AND a.column_name = b.column_name";
		sql += "                AND b.table_name = ?";
		sql += "          LEFT JOIN";
		sql += "             (SELECT   e.table_name,";
		sql += "                       e.column_name,";
		sql += "                       f.table_name r_table_name,";
		sql += "                       f.column_name r_column_name,";
		sql += "                       d.constraint_type";
		sql += "                FROM         user_constraints d";
		sql += "                          LEFT JOIN";
		sql += "                             user_cons_columns e";
		sql += "                          ON d.constraint_name = e.constraint_name";
		sql += "                             AND e.table_name = ?";
		sql += "                       LEFT JOIN";
		sql += "                          user_cons_columns f";
		sql += "                       ON d.r_constraint_name = f.constraint_name";
		sql += "               WHERE       d.constraint_type IN ('R', 'P', 'U')";
		sql += "                       AND d.status = 'ENABLED'";
		sql += "                       AND d.table_name = ?) c";
		sql += "          ON a.table_name = c.table_name AND a.column_name = c.column_name";
		sql += "  WHERE   a.table_name = ?";
		List params = new LinkedList();
		params.add(tableName);
		params.add(tableName);
		params.add(tableName);
		params.add(tableName);
		String orders = "";
		sql += orders;
		return jdbcTemplate.queryForList(sql, params.toArray());
	}
}
